#!/bin/bash

scriptPath=$(dirname $0);


attach="attach 'trace.sqlite' as trace; attach 'match.sqlite' as match;"

# create match.sqlite to collect the results
if [ -f match.sqlite ]
then
	rm match.sqlite
fi

query="CREATE TABLE match (dataobjectId, nodeName, isNodeDescription, ratio, rank, isReceived, isOwnData, createTime, receiveTime, numHop, createdDuringContact);"
sqlite3 -batch match.sqlite "$query"

insertQuery="INSERT INTO match (dataobjectId, nodeName, isNodedescription, ratio, isReceived, isOwnData, createTime, receiveTime)"



# loop through all nodes
for node in $(sqlite3 -batch all-nodes.db "SELECT name FROM table_nodes WHERE type=3 OR type=1 ORDER BY name;")
do
	echo $node

	# replace dynamic views
	# max_nodes_to_attributes
	query="DROP VIEW view_map_nodes_to_attributes_via_rowid_dynamic;"
	sqlite3 -batch all-nodes.db "$query"
	
	query="CREATE VIEW view_map_nodes_to_attributes_via_rowid_dynamic AS SELECT na.* FROM table_map_nodes_to_attributes_via_rowid as na LEFT JOIN table_nodes as n ON na.node_rowid=n.rowid WHERE n.name='$node';"
	sqlite3 -batch all-nodes.db "$attach $query"

	# replace dynamic views
	# limit events to EVENT_TYPE_DATAOBJECT_NEW of Data for faster matching (mark node descriptions)
	query="CREATE VIEW eventsNew AS SELECT * FROM events as e WHERE e.eventName='EVENT_TYPE_DATAOBJECT_NEW' AND device='$node'"
	sqlite3 -batch trace.sqlite "$query"
	
	query="CREATE VIEW eventsIncomingOwn AS SELECT * FROM events as e WHERE eventName='EVENT_TYPE_DATAOBJECT_INCOMING' AND substr(nodeId,1,3)='app' and device='$node';"
	sqlite3 -batch trace.sqlite "$query"

	# analysis [node, isNodeDescription, ratio, isReceived, isOwnData, createTime, receiveTime]
	query="SELECT lower(hex(d.id)) as relevant, '$node', (d.xmlhdr LIKE '%NodeDescription%') isNodeDescription, m.ratio as ratio, (e.time NOT NULL) as isReceived, (o.time NOT NULL) as isOwnData, d.createtime as createtime, e.time*1000 as receivetime FROM view_match_nodes_and_dataobjects_as_ratio as m LEFT JOIN table_dataobjects as d ON m.dataobject_rowid=d.rowid LEFT JOIN trace.eventsNew as e ON lower(hex(d.id))=substr(e.dataobjectId,1,40) AND e.device='$node' LEFT JOIN trace.eventsIncomingOwn as o ON lower(hex(d.id))=substr(o.dataobjectId,1,40) AND o.device='$node' ORDER BY ratio desc, isReceived desc;"
	sqlite3 -batch -separator '	'  all-nodes.db "$attach $insertQuery $query"
	

	query="DROP VIEW eventsNew;"
	sqlite3 -batch trace.sqlite "$query"
	
	query="DROP VIEW eventsIncomingOwn;"
	sqlite3 -batch trace.sqlite "$query"

done

	# summary
	query="SELECT nodeName, count(*), sum(isReceived), 100*sum(isReceived)/count(*) FROM match WHERE isNodeDescription=0 AND ratio>10 GROUP BY nodeName;"
	sqlite3 -batch match.sqlite "$query"

# sh $scriptPath/matchRank.sh

